﻿' ------------------------------------------------------------------------------
' A TimeZone object contains information about a time zone that was
' obtained from the TimeZone table in the database. It includes a polygon
' or multipolyon that defines the timezone's geometery.
'
' The static method SpatialSearchTimeZone() performs a spatial query
' against the database to find the time zone containing a particular
' location.
'
' The static method GetTimeZones retrieves all the data from the
' TimeZone table and returns a Dictionary of TimeZone objects.
'
' Time zone polygons are stored in the database as geometry types
' instead of geography types. This is necessary because some time zone
' polygons violate the geography constraint that a feature be confined
' to a single hemisphere. Many time zones include both poles and so
' violate this constraint.
'
' The ColorID contains an integer for symbolizing the time zones with an
' alternating pair of colors.
' ------------------------------------------------------------------------------
' This file is part of the SQL Server 2008 Technology Demonstration. This
' application was designed and coded by Eric Weitzman, LandTime Inc, for
' Microsoft Corporation. Contact Ed Katibah at Microsoft about this demo.
' Copyright © 2008 Microsoft Corporation. All rights reserved.
' ------------------------------------------------------------------------------

Public Class TimeZone

    Public UniqueID As Integer
    Public TimeZoneCode As String
    Public UtcOffset As Double
    Public CivTimeZoneCode As String
    Public CivTimeZoneDescription As String
    Public ColorID As Integer
    Public TimeZonePolygon As SqlGeometry

    Private Shared m_timezones As TimeZoneDictionary = Nothing

    Private Class TimeZoneDictionary
        Inherits Dictionary(Of Integer, TimeZone)
    End Class

    Private Sub New() ' inaccessible constructor
    End Sub

    Public Sub New(ByVal id As Integer, ByVal zc As String, ByVal offset As Double, ByVal ctzc As String, ByVal ctzd As String, ByVal clr As Integer, ByVal poly As SqlGeometry)
        UniqueID = id
        TimeZoneCode = zc
        UtcOffset = offset
        CivTimeZoneCode = ctzc
        CivTimeZoneDescription = ctzd
        ColorID = clr
        TimeZonePolygon = poly
    End Sub

    Public Shared Function SpatialSearchTimeZone(ByVal longitude As Double, ByVal latitude As Double, ByVal connection As Connection) As TimeZone

        Dim table As String = Schema.TimeZone

        Dim cmd As SqlCommand = connection.CreateCommand()
        Dim tz As TimeZone = Nothing
        Dim rdr As SqlDataReader = Nothing

        Try
            ReportTrace("searching for timezone polygon in table " & table & " ...")

            ' searching a GEOMETRY column, point coordinate order is (x,y) = (longitude, latitude)
            Dim point As String = CreateWktGeometryPoint(longitude, latitude)
            Dim columns As String = "ShapeID, TimeZoneCode, UtcOffset, CivTimeZoneCode, CivTimeZoneDescription, ColorID, ZonePolygon"

            cmd.CommandText = String.Format("SELECT {0} FROM {1} WHERE 1 = ZonePolygon.STContains(@Location)", columns, table)
            cmd.Parameters.AddWithValue("@Location", point)
            ReportSqlCommand("spatial select timezone", cmd)
            rdr = cmd.ExecuteReader(CommandBehavior.SingleRow)

            If rdr.HasRows Then
                rdr.Read()
                Dim wkt As String = CType(rdr.Item(6), SqlGeometry).STAsText.Value
                If wkt.Length > 100 Then
                    wkt = wkt.Substring(0, 100) & "..."
                End If

                ReportTrace(String.Format("row: {0} | {1} | {2} | {3} | {4} | {5} | {6} ", rdr.Item(0), rdr.Item(1), rdr.Item(2), rdr.Item(3), rdr.Item(4), rdr.Item(5), wkt))
                ReportTrace("... found polygon")

                tz = New TimeZone(CType(rdr.Item(0), Integer), _
                                         CType(rdr.Item(1), String), _
                                         CType(rdr.Item(2), Double), _
                                         CType(rdr.Item(3), String), _
                                         CType(rdr.Item(4), String), _
                                         CType(rdr.Item(5), Integer), _
                                         CType(rdr.Item(6), SqlGeometry))
            Else
                ReportTrace("... not found")
                'tz = SpatialSearchInternally(longitude, latitude)
            End If

        Catch ex As Exception
            ReportException(ex)
        Finally
            If rdr IsNot Nothing Then rdr.Close()
        End Try

        Return tz

    End Function

    'Public Shared Function SpatialSearchInternally(ByVal longitude As Double, ByVal latitude As Double) As TimeZone
    '    Dim wktpoint As String = CreateWktGeometryPoint(longitude, latitude)
    '    Dim sqlchars As New System.Data.SqlTypes.SqlChars(wktpoint)
    '    Dim loc As SqlGeometry = SqlGeometry.STGeomFromText(sqlchars, 4326)

    '    For Each tz As TimeZone In m_timezones.Values
    '        If tz.CivTimeZoneCode.Trim = "CCT" Then
    '            Dim bnd As SqlGeometry = tz.TimeZonePolygon.STEnvelope
    '            sqlchars = bnd.STAsText()
    '            Dim wkt As String = sqlchars.Value
    '        End If
    '        If tz.TimeZonePolygon.STContains(loc) Then
    '            Return tz
    '        End If
    '    Next
    '    Return Nothing
    'End Function

    Public Shared Function GetTimeZones(ByVal connection As Connection) As Dictionary(Of Integer, TimeZone)

        If m_timezones IsNot Nothing Then Return m_timezones

        m_timezones = New TimeZoneDictionary

        Dim table As String = Schema.TimeZone

        Dim rdr As SqlDataReader = Nothing

        Try
            Dim columns As String = "ShapeID, TimeZoneCode, UtcOffset, CivTimeZoneCode, CivTimeZoneDescription, ColorID, ZonePolygon"

            Dim cmd As SqlCommand = connection.CreateCommand()
            cmd.CommandText = String.Format("SELECT {0} FROM {1}", columns, table)
            ReportSqlCommand("load timezones", cmd)
            rdr = cmd.ExecuteReader()

            While rdr.Read()
                Dim tz As TimeZone = New TimeZone(CType(rdr.Item(0), Integer), _
                                                  CType(rdr.Item(1), String), _
                                                  CType(rdr.Item(2), Double), _
                                                  CType(rdr.Item(3), String), _
                                                  CType(rdr.Item(4), String), _
                                                  CType(rdr.Item(5), Integer), _
                                                  CType(rdr.Item(6), SqlGeometry))
                m_timezones.Add(tz.UniqueID, tz)
            End While

        Catch ex As Exception
            ReportException(ex)
        Finally
            If rdr IsNot Nothing Then rdr.Close()
        End Try

        Return m_timezones
    End Function

End Class
